package cn.wsalix.admin.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import cn.anlaser.repository.CommonRepository;
import cn.wsalix.admin.entity.SysUser;

public interface UserRepository extends CommonRepository<SysUser> {

	@Query(value = "select u from SysUser u where u.username = :username")
	public SysUser findByUsernameOne(@Param("username") String username);

	//@Query("select user from (select user,userrole.role.id user_role   from SysUser user    left outer join (select ur from UserRole ur  where role.id =:roleId) userrole on user.id= userrole.user.id) user    where user_role is null")
	//@Query(nativeQuery = true,value = "select user from (select user,userrole.role.id user_role from SysUser user    left outer join (select ur from UserRole ur  where role.id = ?1) userrole on user.id= userrole.user.id) user    where user_role is null")	
	@Query(nativeQuery = true,value = "select user.* from (select admin_user.*,userrole.role_id as user_role from WOW_ADMIN_USER admin_user    left outer join (select ur.* from WOW_ADMIN_USER_ROLE ur  where ur.role_id = ?1) userrole on admin_user.id= userrole.user_id) user    where user_role is null")
	public List<SysUser> findUnUsersByRoleId(@Param("roleId") Long roleId);

	@Query("select u from UserRole ur,SysUser u where ur.user.id = u.id and ur.role.id=:roleId")
	public List<SysUser> findUsersByRoleId(@Param("roleId") Long roleId);
	
	@Query(value = "select u from SysUser u where u.username = :username")
	public List<SysUser> findByUsername(@Param("username") String username);

}
